
/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */
package DAO;

import DTO.*;
import UTIL.*;
import java.sql.Statement;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.text.SimpleDateFormat;
import java.util.Date;
import javax.swing.JOptionPane;

/**
 *
 * @author ldtung_it
 */
public class MailDAO {
    /**
     * get id mail
     * @return
     * @throws java.sql.SQLException
     */
    public static int getIDMail() throws SQLException{
         Connection connect = null;
        try {
            connect = SqlDataAccessHelper.getConnection();
            Statement stmt = (Statement) connect.createStatement();
            stmt = (Statement) connect.createStatement();
            String sqlQuery = "select max(IDMail)as ID from Mail";
            ResultSet newCode = stmt.executeQuery(sqlQuery);
            newCode.next();
            
            int newID = newCode.getInt(1);
            newCode.close();
            stmt.close();
            connect.close();
            return newID;
        }
        catch(Exception ex){
            
        }
         connect.close();
         return 0;
    }

    /**
     * add a new Mail
     * @param mail
     * @return
     * @throws java.sql.SQLException
     */
    public static int add(MailDTO mail) throws SQLException {
        Connection connect = null;
        try {
            connect = SqlDataAccessHelper.getConnection();
            Statement stmt = (Statement) connect.createStatement();
            Date date = mail.get_datetimeSent();
            SimpleDateFormat dateFormatter = new SimpleDateFormat("MM/dd/yyyy hh:mm:ss aa");
            
            String sqlQuery = "insert into Mail(IDSender,Subject,Content,DateTimeSent,MailIn,TypeMail) values(" + mail.get_idSender() + ",'" + mail.get_subject() + "','" + mail.get_content() + "','" + dateFormatter.format(date) + "'," + mail.get_mailIn() + "," + mail.get_typeMail() + ")";
            stmt.executeUpdate(sqlQuery);

            
            stmt.close();            
            connect.close();
            return MailDAO.getIDMail();
        } catch (SQLException ex) {
            JOptionPane.showMessageDialog(null, "Fail to connect or mistake to add!");
        }
        connect.close();
        return 0;
    }
    /**
     * delete mail which have id mail = idmail
     * @param idMail
     * @return
     * @throws java.sql.SQLException
     */

    public static int delete(int idMail) throws SQLException {
        Connection connect = null;
        try {
            connect = SqlDataAccessHelper.getConnection();
            Statement stmt = (Statement) connect.createStatement();
            String sqlQuery = "";
            sqlQuery = "delete from Mail where IDMail = " + idMail;
            stmt.executeUpdate(sqlQuery);
            stmt.close();
            connect.close();
            return 1;
        } catch (SQLException ex) {
            JOptionPane.showMessageDialog(null, "Fail to connect or mistake to delete!");
            connect.close();
        }
        return 0;
    }
}
